package com.student.system.dao;

import com.student.system.entity.*;
import com.student.system.utils.C3P0Untils;
import com.student.system.utils.MD5Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class StudentDao {
    private QueryRunner queryRunner=new QueryRunner(C3P0Untils.getDataSource());

    private static final String StudentMessage_FIELD_SQL = "`id`,`name`,`studentId`,`phone`,`identity`,`sex`,`age`,`email`,`major`,`identityId`";
    private static final String StudentRegister_FIELD_SQL = "`id`,`userName`,`phone`,`password`";
    private static final String StudentScore_FIELD_SQL = "`id`,`name`,`subject`,`score`";
    public void save(Studentregister studentregister) throws SQLException {
        String sql = "INSERT into `st_register`(`userName`,`phone`,`password`) values(?,?,?)";
        queryRunner.update(sql,studentregister.getUserName(),studentregister.getPhone(),studentregister.getPassword());
    }

    public void savem(Studentmessage studentmessage) throws SQLException {
        String sql = "INSERT into `studentm`(`name`,`studentId`,`phone`,`identity`,`sex`,`age`,`email`," +
                "`major`,`identityId`) values(?,?,?,?,?,?,?,?,?)";
        queryRunner.update(sql,studentmessage.getName(),studentmessage.getStudentId(),studentmessage.getPhone()
                ,studentmessage.getIdentity(),studentmessage.getSex(),studentmessage.getAge(),studentmessage.getEmail(),
                studentmessage.getMajor(),studentmessage.getIdentityId()
        );
    }

    public Studentregister findByusername(String userName) throws SQLException {
        String sql = "select * from `st_register` where userName=?";
        return queryRunner.query(sql,new BeanHandler<>(Studentregister.class),userName);
    }

    /***
     *
     * @return
     */
    public List<Studentmessage> list() throws SQLException {
        String sql = "select"+StudentMessage_FIELD_SQL+" from `studentm`";
        return queryRunner.query(sql,new BeanListHandler<>(Studentmessage.class));
    }

    public List<Studentregister> registerList() throws SQLException {
        String sql = "select * from `st_register`";
        return queryRunner.query(sql,new BeanListHandler<>(Studentregister.class));
    }

    public Studentmessage selectById(int id) throws SQLException {
        String sql = "select"+StudentMessage_FIELD_SQL+" from `studentm` where id=?";
        return queryRunner.query(sql,new BeanHandler<>(Studentmessage.class),id);
    }


    public void doEditStudentMessage(Studentmessage studentmessage) throws SQLException {
        String sql="UPDATE `studentm` set `studentId`=?,`phone`=?,`identity`=?,`email`=? where id=?";
        queryRunner.update(sql,studentmessage.getStudentId(),studentmessage.getPhone(),studentmessage.getIdentity(),studentmessage.getEmail(),studentmessage.getId());
    }

    public Studentregister selectRegisterById(int id) throws SQLException {
        String sql = "select"+StudentRegister_FIELD_SQL+" from `st_register` where id=?";
        return queryRunner.query(sql,new BeanHandler<>(Studentregister.class),id);
    }

    public void doEditStudentRegister(Studentregister studentregister) throws SQLException {
        String sql="UPDATE `st_register` set `phone`=?,`password`=? where id=?";
        queryRunner.update(sql,studentregister.getPhone(),studentregister.getPassword(),studentregister.getId());
    }

    public boolean checkLoginId(String userName) throws SQLException {
        String sql = "SELECT count(*) FROM `st_register` where userName=?";
        long count = (long) queryRunner.query(sql, new ScalarHandler(), userName);
        if (count > 0) {
            return true;
        } else {
            return false;
        }
    }

    public List<StudentScore> studentScoreList() throws SQLException {
        String sql = "select"+StudentScore_FIELD_SQL+" from `student_score`";
        return queryRunner.query(sql,new BeanListHandler<>(StudentScore.class));
    }
}
